# -*- coding: utf-8 -*-
from itertools import chain

__author__ = 'sunnychou'
__date__ = '2019/9/16 16:19'

#导入依赖库
from common.pandas_helper import PandasHelper
from common.utils import FVDateTime, get_month, series_value, increase_rate, get_quarter_index, get_thisyear_month_list
import pandas as pd
from common.logger_helper import g_wlogger
from common.email_helper import email_send_with_appendix_list

'''
功能：
   将月数据统计表进行合并，产生大数据统计表，写成excel文件，发送邮件

统计月报表
    出货统计-产品分类
    出货统计-客户分类
    出货统计-客户经理

计算时间点：
    月底（最后半天）计算本月所有数据

表结构：
   出货统计-产品分类  ---  k3chtjcp
   出货统计-客户分类  ---  k3chtjkh
   出货统计-客户经理  ---  k3chtjjl
   
处理过程：
  1. 由月份获取当月的合并数据，返回dataframe数据集
  2. 将dataframe数据集直接写excel
  3. 发邮件(见common模块中的email_helper.py)
'''
CHTJ_DICT = {
    'k3xstjcp':"select   pp, cpjl, cpgly, dyche, sjsj    from  k3xstjcp where   sjsj  BETWEEN '%s  00:00:00' and '%s  23:59:59'",
    'k3xstjkh':"select   ghdw, khjl, kf, dyche, sjsj    from  k3xstjkh where   sjsj  BETWEEN '%s  00:00:00' and '%s  23:59:59'",
    'k3xstjxs':"select  khjl ,kf,dyche,sjsj from k3xstjxs where   sjsj  BETWEEN '%s  00:00:00' and '%s  23:59:59'",

}


def generate_sql(type, begin_time, end_time):
    target_tb_sql = CHTJ_DICT[type] % (begin_time, end_time)
    return target_tb_sql


def create_df_k3xstjcp(month=None):
    # '''
    # :param month: 输入的月份，默认是本月
    # :return:
    # '''
    # #查询品牌，产品经理，产品助理，当月出库额，时间范围为：当年上月，去年同月，当年当前季度，去年同季度
    # if month == None:
    #     month = get_month(FVDateTime().to_dict()['this_month_start'])
    #
    # fvdate_dict = FVDateTime(month).to_dict()
    #
    # #获取所有的从去年到今年上月累计数据(时间范围：去年第一个月，今年上个月最后一天
    # last_year_start = fvdate_dict['last_year_start']
    # this_month_end = fvdate_dict['this_month_end']
    # target_tb_sql = generate_sql("k3xstjcp", last_year_start, this_month_end)
    # target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    # if target_full_df.empty:
    #     g_wlogger.werror("create_df_k3xstjcp:target_full_df, all data is empty.")
    #     return pd.DataFrame()
    #
    # #待计算数值字段进行转换
    # target_full_df['dyche'] = pd.to_numeric(target_full_df['dyche'])
    #
    # # 今年本月
    # this_month = get_month(this_month_end)
    # #查询本年度本月的出货额
    # month_cond = f'sjsj==["{this_month}"]'
    # this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # #判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    # this_year_this_month_group_series = this_year_this_month_df.groupby('pp')['dyche'].sum() if not this_year_this_month_df.empty else pd.DataFrame()
    #
    # #去年本月
    # last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    # month_cond = f'sjsj==["{last_year_this_month}"]'
    # last_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # last_year_this_month_group_series = last_year_this_month_df.groupby('pp')['dyche'].sum() if not last_year_this_month_df.empty else pd.DataFrame()
    #
    # #今年当前季度
    # this_quarter_start = fvdate_dict['this_quarter_start']
    # this_quarter_end = fvdate_dict['this_quarter_end']
    # this_quarter_start_month = get_month(this_quarter_start)
    # this_quarter_end_month = get_month(this_quarter_end)
    # month_cond = (target_full_df['sjsj'] >= this_quarter_start_month) & (target_full_df['sjsj'] <= this_quarter_end_month)
    # this_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # this_year_quarter_group_series = this_year_quarter_df.groupby('pp')['dyche'].sum()  if not this_year_quarter_df.empty else pd.DataFrame()
    #
    #
    # #去年当前季度
    # last_year_quarter_start = fvdate_dict['last_year_quarter_start']
    # last_year_quarter_end = fvdate_dict['last_year_quarter_end']
    # last_year_quarter_start_month = get_month(last_year_quarter_start)
    # last_year_quarter_end_month = get_month(last_year_quarter_end)
    # month_cond = (target_full_df['sjsj'] >= last_year_quarter_start_month) & (target_full_df['sjsj'] <= last_year_quarter_end_month)
    # last_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # last_year_quarter_group_series = last_year_quarter_df.groupby('pp')['dych' \
    #                                                                     'e'].sum() if not last_year_quarter_df.empty else pd.DataFrame()
    #
    # #将今年本月，去年本月， 今年当前季度，去年当前季度的品牌进行合并去重
    # #target_total_df = this_year_this_month_df +  last_year_this_month_df + this_year_quarter_df +  last_year_quarter_df
    # #pd.concat([this_year_this_month_df, last_year_this_month_df], axis=0, join='inner')
    # target_total_df = target_full_df
    # target_chtjcp_base_df = target_total_df[['pp','cpjl','cpgly']].drop_duplicates()   #获取待分析的品牌，产品经理，产品助理
    # target_pp_series = target_total_df['pp'].drop_duplicates()    #获取品牌
    # df_data_list = []
    # for pp  in  target_pp_series:
    #     base_df = target_chtjcp_base_df.query(f'pp==["{pp}"]')
    #     cpjl = base_df['cpjl'].values[0]
    #     cpzl = base_df['cpgly'].values[0]
    #     #获取本年本月出货额
    #     this_year_this_month_dyche = 0  if this_year_this_month_group_series.empty else series_value(this_year_this_month_group_series, pp)
    #     # 获取上年本月出货额
    #     last_year_this_month_dyche =  0   if   last_year_this_month_group_series.empty else   series_value(last_year_this_month_group_series, pp)
    #     #月同比增长率
    #     this_month_incre_rate = increase_rate(this_year_this_month_dyche,last_year_this_month_dyche)
    #     # 获取本年本季度出货额
    #     this_year_quarter_dyche =   0  if  this_year_quarter_group_series.empty  else  series_value(this_year_quarter_group_series, pp)
    #     # 获取上年本季度出货额
    #     last_year_quarter_dyche =  0  if  last_year_quarter_group_series.empty else  series_value(last_year_quarter_group_series, pp)
    #     # 季度同比增长率
    #     this_quarter_incre_rate = increase_rate(this_year_quarter_dyche, last_year_quarter_dyche)
    #     #一条记录
    #     if (this_year_this_month_dyche == 0) and (last_year_this_month_dyche == 0) and (this_year_quarter_dyche == 0) and (last_year_quarter_dyche == 0):
    #         continue
    #     item_tuple = (pp, cpjl, cpzl, this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate,
    #                   this_year_quarter_dyche, last_year_quarter_dyche, this_quarter_incre_rate)
    #     df_data_list.append(item_tuple)
    #
    # this_year = month.split("-")[0]
    # last_year = last_year_this_month.split("-")[0]
    # #说明：此处的columns列一定要和上述item_tuple顺序对应上
    # columns = ["品牌", "产品经理", "产品管理员",
    #            f"{month}月销售额", f"{last_year_this_month}月销售额", f"{this_year}年较{last_year}同期月增长率",
    #            f"{this_year}年本季度销售额", f"{last_year}年本季度销售额", f"{this_year}年较{last_year}同期季度增长率"]
    # k3chtjcp_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns)
    if month == None:
        month = get_month(FVDateTime().to_dict()['this_month_start'])

    fvdate_dict = FVDateTime(month).to_dict()
    last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    # 获取所有的从去年到今年上月累计数据(时间范围：去年第一个月，今年上个月最后一天
    last_year_start = fvdate_dict['last_year_start']
    this_month_end = fvdate_dict['this_month_end']
    target_tb_sql = generate_sql("k3xstjcp", last_year_start, this_month_end)
    target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    if target_full_df.empty:
        g_wlogger.werror("create_df_k3xstjcp:target_full_df, all data is empty.")
        return pd.DataFrame()

    # 待计算数值字段进行转换
    target_full_df['dyche'] = pd.to_numeric(target_full_df['dyche'])

    # 获取从今年1月份到当前月份的列表数据
    month_list = get_thisyear_month_list(month)
    target_group_series_dict = {}
    for month in month_list:
        result = cal_the_month_k3xstjcp_df(target_full_df, month)  # 返回的result是一个四元组，简写
        target_group_series_dict[month] = result

    # 将今年本月，去年本月， 今年当前季度，去年当前季度的品牌进行合并去重
    # target_total_df = this_year_this_month_df +  last_year_this_month_df + this_year_quarter_df +  last_year_quarter_df
    # pd.concat([this_year_this_month_df, last_year_this_month_df], axis=0, join='inner')
    target_total_df = target_full_df
    target_chtjcp_base_df = target_total_df[['pp','cpjl','cpgly']].drop_duplicates()  # 获取待分析的品牌，产品经理，产品助理
    target_ghdw_series = target_total_df['pp'].drop_duplicates()  # 获取品牌
    df_data_list = []
    for pp in target_ghdw_series:
        base_df = target_chtjcp_base_df.query(f'pp==["{pp}"]')
        khjl = base_df['cpjl'].values[0]
        kf = base_df['cpgly'].values[0]
        if pp == "":
            continue
        the_month_data_list = []
        for the_month in month_list:  # 循环月份
            (this_year_this_month_group_series, last_year_this_month_group_series, \
             this_year_quarter_group_series, last_year_quarter_group_series) = target_group_series_dict[the_month]
            # 获取本年本月出货额
            this_year_this_month_dyche = 0 if this_year_this_month_group_series.empty else series_value(
                this_year_this_month_group_series, pp)
            # 获取上年本月出货额
            last_year_this_month_dyche = 0 if last_year_this_month_group_series.empty else series_value(
                last_year_this_month_group_series, pp)
            # 月同比增长率
            this_month_incre_rate = increase_rate(this_year_this_month_dyche, last_year_this_month_dyche)

            # notes: 季度数据需要根据月份来判断是否添加， 3， 6， 9， 12 月份这四个月后面加季度数据
            quarter_index = get_quarter_index(the_month)
            if quarter_index == 0:  # 不需要添加季度数据的月份
                item_tuple = (this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate)
            else:  # 需要追加季度数据的月份
                # 获取本年本季度出货额
                this_year_quarter_dyche = 0 if this_year_quarter_group_series.empty else series_value(
                    this_year_quarter_group_series, pp)
                # 获取上年本季度出货额
                last_year_quarter_dyche = 0 if last_year_quarter_group_series.empty else series_value(
                    last_year_quarter_group_series, pp)
                # 季度同比增长率
                this_quarter_incre_rate = increase_rate(this_year_quarter_dyche, last_year_quarter_dyche)

                # 一条记录
                if (this_year_this_month_dyche == 0) and (last_year_this_month_dyche == 0) and (
                        this_year_quarter_dyche == 0) and (last_year_quarter_dyche == 0):
                    continue

                item_tuple = (this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate,
                              this_year_quarter_dyche, last_year_quarter_dyche, this_quarter_incre_rate)
            the_month_data_list.append(item_tuple)
        tmp_month_list = list(chain.from_iterable(the_month_data_list))  # 展开列表
        tmp_month_list.insert(0, pp)
        tmp_month_list.insert(1, khjl)
        tmp_month_list.insert(2, kf)
        df_data_list.append(tmp_month_list)

    # 说明：此处的columns列一定要和上述item_tuple顺序对应上
    # columns = ["客户经理",
    #             f"{month}月出货额", f"{last_year_this_month}月出货额",f"{this_year}年较{last_year}同期月增长率",
    #             f"{this_year}年本季度出货额", f"{last_year}年本季度出货额",f"{this_year}年较{last_year}同期季度增长率"]
    columns_list = []
    for month in month_list:
        this_year = month.split("-")[0]
        this_month = month.split("-")[1]
        fvdate_dict = FVDateTime(month).to_dict()
        last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
        last_year = last_year_this_month.split("-")[0]
        quarter_index = get_quarter_index(month)
        if quarter_index == 0:  # 不需要添加季度数据的月份
            columns = [f"{month}月销售额", f"{last_year_this_month}月销售额", f"{month}较{last_year_this_month}同期月增长率"]
        else:
            columns = [f"{month}月销售额", f"{last_year_this_month}月销售额", f"{month}较{last_year_this_month}同期月增长率",
                       f"{this_year}-{quarter_index}季度销售额", f"{last_year}-{quarter_index}季销售货额",
                       f"{this_year}较{last_year}同期{quarter_index}季度增长率"]
        columns_list.append(columns)
    columns_list = list(chain.from_iterable(columns_list))  # 展开列表
    columns_list.insert(0, "品牌")
    columns_list.insert(1, "产品经理")
    columns_list.insert(2, "产品管理员")
    k3chtjcp_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns_list)
    return k3chtjcp_df
def create_df_k3xstjkh(month=None):
    # '''
    # :param month: 输入的月份，默认是本月
    # :return:
    # '''
    # #查询品牌，产品经理，产品助理，当月出库额，时间范围为：当年上月，去年同月，当年当前季度，去年同季度
    # if month == None:
    #     month = get_month(FVDateTime().to_dict()['this_month_start'])
    #
    # fvdate_dict = FVDateTime(month).to_dict()
    #
    # #获取所有的从去年到今年上月累计数据(时间范围：去年第一个月，今年上个月最后一天
    # last_year_start = fvdate_dict['last_year_start']
    # this_month_end = fvdate_dict['this_month_end']
    # target_tb_sql = generate_sql("k3xstjkh", last_year_start, this_month_end)
    # target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    # if target_full_df.empty:
    #     g_wlogger.werror("create_df_k3xstjkh:target_full_df, all data is empty.")
    #     return pd.DataFrame()
    #
    # #待计算数值字段进行转换
    # target_full_df['dyche'] = pd.to_numeric(target_full_df['dyche'])
    #
    # # 今年本月
    # this_month = get_month(this_month_end)
    # #查询本年度本月的出货额
    # month_cond = f'sjsj==["{this_month}"]'
    # this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # #判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    # this_year_this_month_group_series = this_year_this_month_df.groupby('ghdw')['dyche'].sum() if not this_year_this_month_df.empty else pd.DataFrame()
    #
    # #去年本月
    # last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    # month_cond = f'sjsj==["{last_year_this_month}"]'
    # last_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # last_year_this_month_group_series = last_year_this_month_df.groupby('ghdw')['dyche'].sum() if not last_year_this_month_df.empty else pd.DataFrame()
    #
    # #今年当前季度
    # this_quarter_start = fvdate_dict['this_quarter_start']
    # this_quarter_end = fvdate_dict['this_quarter_end']
    # this_quarter_start_month = get_month(this_quarter_start)
    # this_quarter_end_month = get_month(this_quarter_end)
    # month_cond = (target_full_df['sjsj'] >= this_quarter_start_month) & (target_full_df['sjsj'] <= this_quarter_end_month)
    # this_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # this_year_quarter_group_series = this_year_quarter_df.groupby('ghdw')['dyche'].sum()  if not this_year_quarter_df.empty else pd.DataFrame()
    #
    #
    # #去年当前季度
    # last_year_quarter_start = fvdate_dict['last_year_quarter_start']
    # last_year_quarter_end = fvdate_dict['last_year_quarter_end']
    # last_year_quarter_start_month = get_month(last_year_quarter_start)
    # last_year_quarter_end_month = get_month(last_year_quarter_end)
    # month_cond = (target_full_df['sjsj'] >= last_year_quarter_start_month) & (target_full_df['sjsj'] <= last_year_quarter_end_month)
    # last_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # last_year_quarter_group_series = last_year_quarter_df.groupby('ghdw')['dych' \
    #                                                                     'e'].sum() if not last_year_quarter_df.empty else pd.DataFrame()
    #
    # #将今年本月，去年本月， 今年当前季度，去年当前季度的品牌进行合并去重
    # #target_total_df = this_year_this_month_df +  last_year_this_month_df + this_year_quarter_df +  last_year_quarter_df
    # #pd.concat([this_year_this_month_df, last_year_this_month_df], axis=0, join='inner')
    # target_total_df = target_full_df
    # target_chtjcp_base_df = target_total_df[['ghdw','khjl','kf']].drop_duplicates()   #获取待分析的品牌，产品经理，产品助理
    # target_ghdw_series = target_total_df['ghdw'].drop_duplicates()    #获取品牌
    # df_data_list = []
    # for ghdw  in  target_ghdw_series:
    #     base_df = target_chtjcp_base_df.query(f'ghdw==["{ghdw}"]')
    #     cpjl = base_df['khjl'].values[0]
    #     cpzl = base_df['kf'].values[0]
    #     #获取本年本月出货额
    #     this_year_this_month_dyche = 0  if this_year_this_month_group_series.empty else series_value(this_year_this_month_group_series, ghdw)
    #     # 获取上年本月出货额
    #     last_year_this_month_dyche =  0   if   last_year_this_month_group_series.empty else   series_value(last_year_this_month_group_series, ghdw)
    #     #月同比增长率
    #     this_month_incre_rate = increase_rate(this_year_this_month_dyche,last_year_this_month_dyche)
    #     # 获取本年本季度出货额
    #     this_year_quarter_dyche =   0  if  this_year_quarter_group_series.empty  else  series_value(this_year_quarter_group_series, ghdw)
    #     # 获取上年本季度出货额
    #     last_year_quarter_dyche =  0  if  last_year_quarter_group_series.empty else  series_value(last_year_quarter_group_series, ghdw)
    #     # 季度同比增长率
    #     this_quarter_incre_rate = increase_rate(this_year_quarter_dyche, last_year_quarter_dyche)
    #     #一条记录
    #     if (this_year_this_month_dyche == 0) and (last_year_this_month_dyche == 0) and (this_year_quarter_dyche == 0) and (last_year_quarter_dyche == 0):
    #         continue
    #     item_tuple = (ghdw, cpjl, cpzl, this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate,
    #                   this_year_quarter_dyche, last_year_quarter_dyche, this_quarter_incre_rate)
    #     df_data_list.append(item_tuple)
    #
    # this_year = month.split("-")[0]
    # last_year = last_year_this_month.split("-")[0]
    # #说明：此处的columns列一定要和上述item_tuple顺序对应上
    # columns = ["购货单位", "客户经理", "客服  ",
    #            f"{month}月销售额", f"{last_year_this_month}月销售额", f"{this_year}年较{last_year}同期月增长率",
    #            f"{this_year}年本季度销售额", f"{last_year}年本季度销售额", f"{this_year}年较{last_year}同期季度增长率"]
    # k3chtjcp_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns)
    if month == None:
        month = get_month(FVDateTime().to_dict()['this_month_start'])

    fvdate_dict = FVDateTime(month).to_dict()
    last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    # 获取所有的从去年到今年上月累计数据(时间范围：去年第一个月，今年上个月最后一天
    last_year_start = fvdate_dict['last_year_start']
    this_month_end = fvdate_dict['this_month_end']
    target_tb_sql = generate_sql("k3xstjkh", last_year_start, this_month_end)
    target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    if target_full_df.empty:
        g_wlogger.werror("create_df_k3xstjkh:target_full_df, all data is empty.")
        return pd.DataFrame()

    # 待计算数值字段进行转换
    target_full_df['dyche'] = pd.to_numeric(target_full_df['dyche'])

    # 获取从今年1月份到当前月份的列表数据
    month_list = get_thisyear_month_list(month)
    target_group_series_dict = {}
    for month in month_list:
        result = cal_the_month_k3xstjkh_df(target_full_df, month)  # 返回的result是一个四元组，简写
        target_group_series_dict[month] = result

    # 将今年本月，去年本月， 今年当前季度，去年当前季度的品牌进行合并去重
    # target_total_df = this_year_this_month_df +  last_year_this_month_df + this_year_quarter_df +  last_year_quarter_df
    # pd.concat([this_year_this_month_df, last_year_this_month_df], axis=0, join='inner')
    target_total_df = target_full_df
    target_chtjcp_base_df = target_total_df[['ghdw','khjl','kf']].drop_duplicates()  # 获取待分析的品牌，产品经理，产品助理
    target_ghdw_series = target_total_df['ghdw'].drop_duplicates()  # 获取品牌
    df_data_list = []
    for ghdw in target_ghdw_series:
        base_df = target_chtjcp_base_df.query(f'ghdw==["{ghdw}"]')
        print(base_df)
        khjl = base_df['khjl'].values[0]
        kf = base_df['kf'].values[0]
        if ghdw == "":
            continue
        the_month_data_list = []
        for the_month in month_list:  # 循环月份
            (this_year_this_month_group_series, last_year_this_month_group_series, \
             this_year_quarter_group_series, last_year_quarter_group_series) = target_group_series_dict[the_month]
            # 获取本年本月出货额
            this_year_this_month_dyche = 0 if this_year_this_month_group_series.empty else series_value(
                this_year_this_month_group_series, ghdw)
            # 获取上年本月出货额
            last_year_this_month_dyche = 0 if last_year_this_month_group_series.empty else series_value(
                last_year_this_month_group_series, ghdw)
            # 月同比增长率
            this_month_incre_rate = increase_rate(this_year_this_month_dyche, last_year_this_month_dyche)

            # notes: 季度数据需要根据月份来判断是否添加， 3， 6， 9， 12 月份这四个月后面加季度数据
            quarter_index = get_quarter_index(the_month)
            if quarter_index == 0:  # 不需要添加季度数据的月份
                item_tuple = (this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate)
            else:  # 需要追加季度数据的月份
                # 获取本年本季度出货额
                this_year_quarter_dyche = 0 if this_year_quarter_group_series.empty else series_value(
                    this_year_quarter_group_series, ghdw)
                # 获取上年本季度出货额
                last_year_quarter_dyche = 0 if last_year_quarter_group_series.empty else series_value(
                    last_year_quarter_group_series, ghdw)
                # 季度同比增长率
                this_quarter_incre_rate = increase_rate(this_year_quarter_dyche, last_year_quarter_dyche)

                # 一条记录
                if (this_year_this_month_dyche == 0) and (last_year_this_month_dyche == 0) and (
                        this_year_quarter_dyche == 0) and (last_year_quarter_dyche == 0):
                    continue

                item_tuple = (this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate,
                              this_year_quarter_dyche, last_year_quarter_dyche, this_quarter_incre_rate)
            the_month_data_list.append(item_tuple)
        tmp_month_list = list(chain.from_iterable(the_month_data_list))  # 展开列表
        tmp_month_list.insert(0, ghdw)
        tmp_month_list.insert(1, khjl)
        tmp_month_list.insert(2, kf)
        df_data_list.append(tmp_month_list)

    # 说明：此处的columns列一定要和上述item_tuple顺序对应上
    # columns = ["客户经理",
    #             f"{month}月出货额", f"{last_year_this_month}月出货额",f"{this_year}年较{last_year}同期月增长率",
    #             f"{this_year}年本季度出货额", f"{last_year}年本季度出货额",f"{this_year}年较{last_year}同期季度增长率"]
    columns_list = []
    for month in month_list:
        this_year = month.split("-")[0]
        this_month = month.split("-")[1]
        fvdate_dict = FVDateTime(month).to_dict()
        last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
        last_year = last_year_this_month.split("-")[0]
        quarter_index = get_quarter_index(month)
        if quarter_index == 0:  # 不需要添加季度数据的月份
            columns = [f"{month}月销售额", f"{last_year_this_month}月销售额", f"{month}较{last_year_this_month}同期月增长率"]
        else:
            columns = [f"{month}月销售额", f"{last_year_this_month}月销售额", f"{month}较{last_year_this_month}同期月增长率",
                       f"{this_year}-{quarter_index}季度销售额", f"{last_year}-{quarter_index}季销售货额",
                       f"{this_year}较{last_year}同期{quarter_index}季度增长率"]
        columns_list.append(columns)
    columns_list = list(chain.from_iterable(columns_list))  # 展开列表
    columns_list.insert(0, "购货单位")
    columns_list.insert(1, "客户经理")
    columns_list.insert(2, "客服")
    k3chtjcp_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns_list)

    return k3chtjcp_df
def create_df_k3xstjxs(month=None):
    # '''
    # :param month: 输入的月份，默认是本月
    # :return:
    # '''
    # #查询品牌，产品经理，产品助理，当月出库额，时间范围为：当年上月，去年同月，当年当前季度，去年同季度
    # if month == None:
    #     month = get_month(FVDateTime().to_dict()['this_month_start'])
    #
    # fvdate_dict = FVDateTime(month).to_dict()
    #
    # #获取所有的从去年到今年上月累计数据(时间范围：去年第一个月，今年上个月最后一天
    # last_year_start = fvdate_dict['last_year_start']
    # this_month_end = fvdate_dict['this_month_end']
    # target_tb_sql = generate_sql("k3xstjxs", last_year_start, this_month_end)
    # target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    # if target_full_df.empty:
    #     g_wlogger.werror("create_df_k3xstjxs:target_full_df, all data is empty.")
    #     return pd.DataFrame()
    #
    # #待计算数值字段进行转换
    # target_full_df['dyche'] = pd.to_numeric(target_full_df['dyche'])
    #
    # # 今年本月
    # this_month = get_month(this_month_end)
    # #查询本年度本月的出货额
    # month_cond = f'sjsj==["{this_month}"]'
    # this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # #判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    # this_year_this_month_group_series = this_year_this_month_df.groupby('khjl')['dyche'].sum() if not this_year_this_month_df.empty else pd.DataFrame()
    #
    # #去年本月
    # last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    # month_cond = f'sjsj==["{last_year_this_month}"]'
    # last_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # last_year_this_month_group_series = last_year_this_month_df.groupby('khjl')['dyche'].sum() if not last_year_this_month_df.empty else pd.DataFrame()
    #
    # #今年当前季度
    # this_quarter_start = fvdate_dict['this_quarter_start']
    # this_quarter_end = fvdate_dict['this_quarter_end']
    # this_quarter_start_month = get_month(this_quarter_start)
    # this_quarter_end_month = get_month(this_quarter_end)
    # month_cond = (target_full_df['sjsj'] >= this_quarter_start_month) & (target_full_df['sjsj'] <= this_quarter_end_month)
    # this_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # this_year_quarter_group_series = this_year_quarter_df.groupby('khjl')['dyche'].sum()  if not this_year_quarter_df.empty else pd.DataFrame()
    #
    #
    # #去年当前季度
    # last_year_quarter_start = fvdate_dict['last_year_quarter_start']
    # last_year_quarter_end = fvdate_dict['last_year_quarter_end']
    # last_year_quarter_start_month = get_month(last_year_quarter_start)
    # last_year_quarter_end_month = get_month(last_year_quarter_end)
    # month_cond = (target_full_df['sjsj'] >= last_year_quarter_start_month) & (target_full_df['sjsj'] <= last_year_quarter_end_month)
    # last_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # last_year_quarter_group_series = last_year_quarter_df.groupby('khjl')['dych' \
    #                                                                     'e'].sum() if not last_year_quarter_df.empty else pd.DataFrame()
    #
    # #将今年本月，去年本月， 今年当前季度，去年当前季度的品牌进行合并去重
    # #target_total_df = this_year_this_month_df +  last_year_this_month_df + this_year_quarter_df +  last_year_quarter_df
    # #pd.concat([this_year_this_month_df, last_year_this_month_df], axis=0, join='inner')
    # target_total_df = target_full_df
    # target_chtjcp_base_df = target_total_df[['khjl','kf']].drop_duplicates()   #获取待分析的品牌，产品经理，产品助理
    # target_khjl_series = target_total_df['khjl'].drop_duplicates()    #获取品牌
    # df_data_list = []
    # for khjl  in  target_khjl_series:
    #     base_df = target_chtjcp_base_df.query(f'khjl==["{khjl}"]')
    #     cpzl = base_df['kf'].values[0]
    #     #获取本年本月出货额
    #     this_year_this_month_dyche = 0  if this_year_this_month_group_series.empty else series_value(this_year_this_month_group_series, khjl)
    #     # 获取上年本月出货额
    #     last_year_this_month_dyche =  0   if   last_year_this_month_group_series.empty else   series_value(last_year_this_month_group_series, khjl)
    #     #月同比增长率
    #     this_month_incre_rate = increase_rate(this_year_this_month_dyche,last_year_this_month_dyche)
    #     # 获取本年本季度出货额
    #     this_year_quarter_dyche =   0  if  this_year_quarter_group_series.empty  else  series_value(this_year_quarter_group_series, khjl)
    #     # 获取上年本季度出货额
    #     last_year_quarter_dyche =  0  if  last_year_quarter_group_series.empty else  series_value(last_year_quarter_group_series, khjl)
    #     # 季度同比增长率
    #     this_quarter_incre_rate = increase_rate(this_year_quarter_dyche, last_year_quarter_dyche)
    #     #一条记录
    #     if (this_year_this_month_dyche == 0) and (last_year_this_month_dyche == 0) and (this_year_quarter_dyche == 0) and (last_year_quarter_dyche == 0):
    #         continue
    #     item_tuple = (khjl,cpzl,  this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate,
    #                   this_year_quarter_dyche, last_year_quarter_dyche, this_quarter_incre_rate)
    #     df_data_list.append(item_tuple)
    #
    # this_year = month.split("-")[0]
    # last_year = last_year_this_month.split("-")[0]
    # #说明：此处的columns列一定要和上述item_tuple顺序对应上
    # columns = ["客户经理","客服",
    #            f"{month}月销售额", f"{last_year_this_month}月销售额",f"{this_year}年较{last_year}同期月增长率",
    #            f"{this_year}年本季度销售额", f"{last_year}年本季度销售额",f"{this_year}年较{last_year}同期季度增长率"]
    # k3chtjcp_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns)
    if month == None:
        month = get_month(FVDateTime().to_dict()['this_month_start'])

    fvdate_dict = FVDateTime(month).to_dict()
    last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    #获取所有的从去年到今年上月累计数据(时间范围：去年第一个月，今年上个月最后一天
    last_year_start = fvdate_dict['last_year_start']
    this_month_end = fvdate_dict['this_month_end']
    target_tb_sql = generate_sql("k3xstjxs", last_year_start, this_month_end)
    target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    if target_full_df.empty:
        g_wlogger.werror("create_df_k3xstjxs:target_full_df, all data is empty.")
        return pd.DataFrame()

    #待计算数值字段进行转换
    target_full_df['dyche'] = pd.to_numeric(target_full_df['dyche'])

    #获取从今年1月份到当前月份的列表数据
    month_list = get_thisyear_month_list(month)
    target_group_series_dict = {}
    for month in month_list:
        result = cal_the_month_k3xstjxs_df(target_full_df, month)   #返回的result是一个四元组，简写
        target_group_series_dict[month] = result


    #将今年本月，去年本月， 今年当前季度，去年当前季度的品牌进行合并去重
    #target_total_df = this_year_this_month_df +  last_year_this_month_df + this_year_quarter_df +  last_year_quarter_df
    #pd.concat([this_year_this_month_df, last_year_this_month_df], axis=0, join='inner')
    target_total_df = target_full_df
    target_chtjcp_base_df = target_total_df[['khjl','kf']].drop_duplicates()   #获取待分析的品牌，产品经理，产品助理
    target_khjl_series = target_total_df['khjl'].drop_duplicates()    #获取品牌
    df_data_list = []
    for khjl  in  target_khjl_series:
        base_df = target_chtjcp_base_df.query(f'khjl==["{khjl}"]')
        kf = base_df['kf'].values[0]
        if khjl == "":
            continue
        the_month_data_list = []
        for the_month in month_list:   #循环月份
            (this_year_this_month_group_series, last_year_this_month_group_series, \
             this_year_quarter_group_series, last_year_quarter_group_series) = target_group_series_dict[the_month]
            #获取本年本月出货额
            this_year_this_month_dyche = 0  if this_year_this_month_group_series.empty else series_value(this_year_this_month_group_series, khjl)
            # 获取上年本月出货额
            last_year_this_month_dyche =  0   if   last_year_this_month_group_series.empty else   series_value(last_year_this_month_group_series, khjl)
            #月同比增长率
            this_month_incre_rate = increase_rate(this_year_this_month_dyche,last_year_this_month_dyche)

            #notes: 季度数据需要根据月份来判断是否添加， 3， 6， 9， 12 月份这四个月后面加季度数据
            quarter_index = get_quarter_index(the_month)
            if quarter_index == 0:   #不需要添加季度数据的月份
                item_tuple = (this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate)
            else:                       #需要追加季度数据的月份
                # 获取本年本季度出货额
                this_year_quarter_dyche =   0  if  this_year_quarter_group_series.empty  else  series_value(this_year_quarter_group_series, khjl)
                # 获取上年本季度出货额
                last_year_quarter_dyche =  0  if  last_year_quarter_group_series.empty else  series_value(last_year_quarter_group_series, khjl)
                # 季度同比增长率
                this_quarter_incre_rate = increase_rate(this_year_quarter_dyche, last_year_quarter_dyche)

                #一条记录
                if (this_year_this_month_dyche == 0) and (last_year_this_month_dyche == 0) and (this_year_quarter_dyche == 0) and (last_year_quarter_dyche == 0):
                    continue

                item_tuple = (this_year_this_month_dyche, last_year_this_month_dyche, this_month_incre_rate,
                          this_year_quarter_dyche, last_year_quarter_dyche, this_quarter_incre_rate)
            the_month_data_list.append(item_tuple)
        tmp_month_list = list(chain.from_iterable(the_month_data_list))   #展开列表
        tmp_month_list.insert(0, khjl)
        tmp_month_list.insert(1, kf)
        df_data_list.append(tmp_month_list)

    #说明：此处的columns列一定要和上述item_tuple顺序对应上
    # columns = ["客户经理",
    #             f"{month}月出货额", f"{last_year_this_month}月出货额",f"{this_year}年较{last_year}同期月增长率",
    #             f"{this_year}年本季度出货额", f"{last_year}年本季度出货额",f"{this_year}年较{last_year}同期季度增长率"]
    columns_list = []
    for month in  month_list:
        this_year = month.split("-")[0]
        this_month = month.split("-")[1]
        fvdate_dict = FVDateTime(month).to_dict()
        last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
        last_year = last_year_this_month.split("-")[0]
        quarter_index = get_quarter_index(month)
        if quarter_index == 0:  # 不需要添加季度数据的月份
            columns = [f"{month}月出货额", f"{last_year_this_month}月出货额", f"{month}较{last_year_this_month}同期月增长率"]
        else:
            columns = [f"{month}月出货额", f"{last_year_this_month}月出货额", f"{month}较{last_year_this_month}同期月增长率",
                   f"{this_year}-{quarter_index}季度出货额", f"{last_year}-{quarter_index}季度出货额", f"{this_year}较{last_year}同期{quarter_index}季度增长率"]
        columns_list.append(columns)
    columns_list = list(chain.from_iterable(columns_list))  # 展开列表
    columns_list.insert(0, "客户经理")
    columns_list.insert(1, "客服")
    k3chtjcp_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns_list)


    return k3chtjcp_df
def cal_the_month_k3xstjkh_df(target_full_df, the_month):
    '''
    获取某一个月的具体统计数据
    :param target_full_df:
    :param the_month:
    :return:
    '''
    fvdate_dict = FVDateTime(the_month).to_dict()
    # 今年本月
    this_month = get_month(fvdate_dict['this_month_end'])
    # 查询本年度本月的出货额
    month_cond = f'sjsj==["{this_month}"]'
    this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # 判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    this_year_this_month_group_series = this_year_this_month_df.groupby('ghdw')[
        'dyche'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    # 去年本月
    last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    month_cond = f'sjsj==["{last_year_this_month}"]'
    last_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    last_year_this_month_group_series = last_year_this_month_df.groupby('ghdw')[
        'dyche'].sum() if not last_year_this_month_df.empty else pd.DataFrame()

    # 今年当前季度
    this_quarter_start = fvdate_dict['this_quarter_start']
    this_quarter_end = fvdate_dict['this_quarter_end']
    this_quarter_start_month = get_month(this_quarter_start)
    this_quarter_end_month = get_month(this_quarter_end)
    month_cond = (target_full_df['sjsj'] >= this_quarter_start_month) & (
                target_full_df['sjsj'] <= this_quarter_end_month)
    this_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    this_year_quarter_group_series = this_year_quarter_df.groupby('ghdw')[
        'dyche'].sum() if not this_year_quarter_df.empty else pd.DataFrame()

    # 去年当前季度
    last_year_quarter_start = fvdate_dict['last_year_quarter_start']
    last_year_quarter_end = fvdate_dict['last_year_quarter_end']
    last_year_quarter_start_month = get_month(last_year_quarter_start)
    last_year_quarter_end_month = get_month(last_year_quarter_end)
    month_cond = (target_full_df['sjsj'] >= last_year_quarter_start_month) & (
                target_full_df['sjsj'] <= last_year_quarter_end_month)
    last_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    last_year_quarter_group_series = last_year_quarter_df.groupby('ghdw')['dych' \
                                                                          'e'].sum() if not last_year_quarter_df.empty else pd.DataFrame()

    return (this_year_this_month_group_series, last_year_this_month_group_series,
            this_year_quarter_group_series, last_year_quarter_group_series)
def cal_the_month_k3xstjcp_df(target_full_df, the_month):
    '''
    获取某一个月的具体统计数据
    :param target_full_df:
    :param the_month:
    :return:
    '''
    fvdate_dict = FVDateTime(the_month).to_dict()
    # 今年本月
    this_month = get_month(fvdate_dict['this_month_end'])
    # 查询本年度本月的出货额
    month_cond = f'sjsj==["{this_month}"]'
    this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # 判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    this_year_this_month_group_series = this_year_this_month_df.groupby('pp')[
        'dyche'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    # 去年本月
    last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    month_cond = f'sjsj==["{last_year_this_month}"]'
    last_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    last_year_this_month_group_series = last_year_this_month_df.groupby('pp')[
        'dyche'].sum() if not last_year_this_month_df.empty else pd.DataFrame()

    # 今年当前季度
    this_quarter_start = fvdate_dict['this_quarter_start']
    this_quarter_end = fvdate_dict['this_quarter_end']
    this_quarter_start_month = get_month(this_quarter_start)
    this_quarter_end_month = get_month(this_quarter_end)
    month_cond = (target_full_df['sjsj'] >= this_quarter_start_month) & (
                target_full_df['sjsj'] <= this_quarter_end_month)
    this_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    this_year_quarter_group_series = this_year_quarter_df.groupby('pp')[
        'dyche'].sum() if not this_year_quarter_df.empty else pd.DataFrame()

    # 去年当前季度
    last_year_quarter_start = fvdate_dict['last_year_quarter_start']
    last_year_quarter_end = fvdate_dict['last_year_quarter_end']
    last_year_quarter_start_month = get_month(last_year_quarter_start)
    last_year_quarter_end_month = get_month(last_year_quarter_end)
    month_cond = (target_full_df['sjsj'] >= last_year_quarter_start_month) & (
                target_full_df['sjsj'] <= last_year_quarter_end_month)
    last_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    last_year_quarter_group_series = last_year_quarter_df.groupby('pp')['dych' \
                                                                          'e'].sum() if not last_year_quarter_df.empty else pd.DataFrame()

    return (this_year_this_month_group_series, last_year_this_month_group_series,
            this_year_quarter_group_series, last_year_quarter_group_series)
def cal_the_month_k3xstjxs_df(target_full_df, the_month):
    '''
    获取某一个月的具体统计数据
    :param target_full_df:
    :param the_month:
    :return:
    '''
    fvdate_dict = FVDateTime(the_month).to_dict()
    # 今年本月
    this_month = get_month(fvdate_dict['this_month_end'])
    # 查询本年度本月的出货额
    month_cond = f'sjsj==["{this_month}"]'
    this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    # 判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    this_year_this_month_group_series = this_year_this_month_df.groupby('khjl')[
        'dyche'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    # 去年本月
    last_year_this_month = get_month(fvdate_dict['last_year_thismonth_start'])
    month_cond = f'sjsj==["{last_year_this_month}"]'
    last_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    last_year_this_month_group_series = last_year_this_month_df.groupby('khjl')[
        'dyche'].sum() if not last_year_this_month_df.empty else pd.DataFrame()

    # 今年当前季度
    this_quarter_start = fvdate_dict['this_quarter_start']
    this_quarter_end = fvdate_dict['this_quarter_end']
    this_quarter_start_month = get_month(this_quarter_start)
    this_quarter_end_month = get_month(this_quarter_end)
    month_cond = (target_full_df['sjsj'] >= this_quarter_start_month) & (
                target_full_df['sjsj'] <= this_quarter_end_month)
    this_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    this_year_quarter_group_series = this_year_quarter_df.groupby('khjl')[
        'dyche'].sum() if not this_year_quarter_df.empty else pd.DataFrame()

    # 去年当前季度
    last_year_quarter_start = fvdate_dict['last_year_quarter_start']
    last_year_quarter_end = fvdate_dict['last_year_quarter_end']
    last_year_quarter_start_month = get_month(last_year_quarter_start)
    last_year_quarter_end_month = get_month(last_year_quarter_end)
    month_cond = (target_full_df['sjsj'] >= last_year_quarter_start_month) & (
                target_full_df['sjsj'] <= last_year_quarter_end_month)
    last_year_quarter_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    last_year_quarter_group_series = last_year_quarter_df.groupby('khjl')['dych' \
                                                                          'e'].sum() if not last_year_quarter_df.empty else pd.DataFrame()

    return (this_year_this_month_group_series, last_year_this_month_group_series,
            this_year_quarter_group_series, last_year_quarter_group_series)


def  k3xstj_dp_main(month):
    #1. 由月份获取当月的合并数据，返回dataframe数据集
    k3xstjcp = create_df_k3xstjcp(month)
    k3xstjkh=create_df_k3xstjkh(month)
    k3xstjxs=create_df_k3xstjxs(month)
    if k3xstjkh.empty and k3xstjxs.empty  and k3xstjcp.empty:
        g_wlogger.werror("k3chtjcp_dp_main:create_df_k3chtjcp return empty, please check.")
        return
    #2. 写excel
    PandasHelper.df_to_excel(k3xstjcp, "k3xstjcp.xls")
    PandasHelper.df_to_excel(k3xstjkh,"k3xstjkh.xls" )
    PandasHelper.df_to_excel(k3xstjxs,"k3chtjjl.xls")
    #3. 发送邮件
    subject = f"业务中心统计报表-{month}"  #邮件主题
    mail_msg = "业务中心统计报表-关于销售统计"  #邮件主体部分内容，可以写成html格式
    appendix_files = ["k3xstjcp.xls","k3xstjkh.xls","k3chtjjl.xls" ]
    email_send_with_appendix_list(subject, mail_msg, appendix_files)


if __name__ == "__main__":
    month = '2019-09'

    # k3xstj_dp_main(month)
    k3chtjcp = create_df_k3xstjxs(month)
    PandasHelper.df_to_excel(k3chtjcp, "k3chtjcp.xls")

